/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.RegionDao;
import com.tesam.gedeon.entity.Network;
import com.tesam.gedeon.entity.Region;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Sistema
 */
@Repository
public class RegionDaoImpl extends SimpleJdbcDaoSupport implements RegionDao{

     @Autowired
      public RegionDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
        }
    
    @Override
    public int getCountAll() {
        return getJdbcTemplate().queryForInt("select count(*) as total from region where deleted = 0");
    }

    @Override
    public int getCountBySearch(Region region) {
        return getJdbcTemplate().queryForInt("select count(*) as total from region re "
                + "inner join network ne on re.networkid = ne.id "
                + "where re.deleted = 0 and upper(re.description) like '%"+region.getDescription().toUpperCase()+"%'");
    }

    @Override
    public List<Region> getListByLimit(String dataToSort, String directionOfOrder, int startLimit, int endLimit) {
        List<Region> result = new ArrayList();
            try{
               String sql = "select re.* , ne.description as networkName from region re " +
                            "inner join network ne on ne.id = re.networkid "+
                            "where re.deleted = 0 " +
                            "order by "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit;
               
               System.out.println(sql);
               
                  List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                    for (Map row : rows) {
		Region region = new Region();
		region.setId((Integer)(row.get("id")));
		region.setDescription((String)row.get("description")); 
		region.setCode((String)row.get("code"));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid"));
                region.setNetwork(network);
		result.add(region);
                
                }
            }catch(Exception e){
            
            }
        return result ;
    }

    @Override
     public List<Region> getRegionById(Region region){
        List<Region> result = new ArrayList();
        
        try{
            String sql = "update region set viewedtime = NOW() where id = "+region.getId();
            
            getJdbcTemplate().update(sql);
            
              sql = "select re.* , ne.description as networkName from region re " +
                            "inner join network ne on ne.id = re.networkid "+
                            "where re.deleted = 0 and re.id = '"+region.getId()+"' " ;
             
               List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
               for (Map row : rows) {
		Region regionE = new Region();
		regionE.setId((Integer)(row.get("id")));
		regionE.setDescription((String)row.get("description")); 
		regionE.setCode((String)row.get("code"));
                regionE.setDateCreated((Timestamp) row.get("datecreated"));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid"));
                regionE.setNetwork(network);
		result.add(regionE);
                
                }
               
        }catch(Exception e){
        System.out.println("getRegionById : "+e.getMessage());
        }
        
        return result;
    }
    
    @Override
    public int saveRegion(Region region){
        int id = 0;
            try{
                 getJdbcTemplate().update("insert into region (createdby , datecreated , status , "
                + "deleted , presence , ownerid , code ,description , networkid) "
                        + "values (? , NOW() , 1 ,"
                        + "0 , 0 , ? , ? ,? , ?)" ,
                region.getCreatedBy() ,region.getOwnerId() ,region.getCode() ,
                region.getDescription() , region.getNetwork().getId());
                
                id = getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
                
            }catch(Exception e){
            
            }
        return id ;
    }
    
    @Override
    public List<Region> getSearchByName(String dataToSort, String directionOfOrder, int startLimit, int endLimit, Region region) {
        List<Region> result = new ArrayList();
            try{
               String sql = "select re.* , ne.description as networkName from region re " +
                            "inner join network ne on ne.id = re.networkid "+
                            "where re.deleted = 0 and upper(re.description) like '%"+region.getDescription().toUpperCase()+"%' " +
                            "order by re."+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit;
                
               
                  List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
               for (Map row : rows) {
		Region regionE = new Region();
		regionE.setId((Integer)(row.get("id")));
		regionE.setDescription((String)row.get("description")); 
		regionE.setCode((String)row.get("code"));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid"));
                regionE.setNetwork(network);
		result.add(regionE);
                
                }
            }catch(Exception e){
            
            }
        return result ;
    }
    
    @Override
    public void delete(Region region){
    getJdbcTemplate().update("update region set deleted = 1 where id = ?",region.getId());
    }
    
    @Override
    public List<Region> updateRegion(Region region){
        List<Region> result = new ArrayList();
            try{
                getJdbcTemplate().update(" update region set modifiedby = ? , datemodified = NOW() , "
                        + "code = ? , description = ? , networkid = ? " +
                          " where id = ? ", region.getModifiedBy() ,
                          region.getCode() , region.getDescription() , region.getNetwork().getId() 
                            , region.getId());
                
                result = this.getRegionById(region);
                
            }catch(Exception e){
            
            }
        return result ;
    }
}
